# -*- coding:utf8 -*-
import yaml
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.styles import colors


# 表格颜色对照网址： https://zhuanlan.zhihu.com/p/154206853


class YamlToExcel(object):

    def __init__(self):
        # 实例化workbook
        self.work_book = Workbook()
        # 激活 worksheet
        self.work_sheet = self.work_book.active

    # 设置sheet表名
    def set_worksheet_title(self, manufacturer):
        self.work_sheet.title = manufacturer

    @classmethod
    def parse(cls):
        with open('source.yaml') as f:
            data = f.read()
        return yaml.load(data, Loader=yaml.Loader)

    # 按照逻辑写入excel
    def write_excel(self):

        # 设置列宽
        self.work_sheet.column_dimensions['A'].width = 17.0
        self.work_sheet.column_dimensions['B'].width = 11.0
        self.work_sheet.column_dimensions['C'].width = 14.0
        self.work_sheet.column_dimensions['D'].width = 11.0
        self.work_sheet.column_dimensions['E'].width = 15.0
        self.work_sheet.column_dimensions['F'].width = 15.0
        self.work_sheet.column_dimensions['G'].width = 40.0
        self.work_sheet.column_dimensions['H'].width = 12.0
        self.work_sheet.column_dimensions['I'].width = 17.0
        self.work_sheet.column_dimensions['J'].width = 17.0
        self.work_sheet.column_dimensions['K'].width = 16.0
        self.work_sheet.column_dimensions['L'].width = 37.0
        self.work_sheet.column_dimensions['M'].width = 40.0

        align = Alignment(horizontal='center', vertical='center', wrap_text=True)  # 设置表格居中

        self.set_worksheet_title('CarSale')

        # 第一列到第十三列 合并单元格
        self.work_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=13)
        # 设置字体颜色
        font_color = Font(color=colors.BLACK, size=12, bold=True)
        self.work_sheet.cell(row=1, column=1, value='选配指南').font = font_color
        self.work_sheet.cell(row=1, column=1, value='选配指南').alignment = align
        # 填充背景颜色（橙色）
        background_color = PatternFill("solid", fgColor="FFBB02")
        self.work_sheet.cell(row=1, column=1).fill = background_color

        # 第二行描述信息
        description_list = [
            '动力类型', '售卖价格', '汽车厂商', '是否停售', '汽车型号', '选配配件', '组件型号', '数量', '国产', '进口', '购买资格', '描述', '备注'
        ]
        column_number = 1
        for title_name in description_list:
            self.work_sheet.cell(row=2, column=column_number, value='{}'.format(title_name)).alignment = align
            column_number = column_number + 1

        # 第三行字段信息
        field_list = [
            'PowerType', 'Price', 'manufacturer', 'IsOnSale', 'CarModel', 'Selection', 'PDKmodel', 'PDKnumber',
            'domesticVersion', 'importVersion', 'IsPurchase', 'description', 'detailsInfo'
        ]
        column_number = 1
        for field_name in field_list:
            self.work_sheet.cell(row=3, column=column_number, value='{}'.format(field_name)).alignment = align
            column_number = column_number + 1

        # 列对照表
        column_compare_dict = {
            "PDKmodel": 7, 'PDKnumber': 8, 'domesticVersion': 9, 'importVersion': 10, 'IsPurchase': 11,
            'description': 12, 'detailsInfo': 13
        }

        # 表格内容写入
        component_start_row = 4
        for component in self.parse():
            # Title ： 角色/套餐ID/厂商/是否停售/型号
            self.work_sheet.cell(row=component_start_row, column=1,
                                 value='{}'.format(component.get('PowerType'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=2,
                                 value='{}'.format(component.get('Price'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=3,
                                 value='{}'.format(component.get('manufacturer'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=4, value='{}'.format(
                component.get('IsOnSale') if component.get('IsOnSale') else '')).alignment = align
            self.work_sheet.cell(row=component_start_row, column=5,
                                 value='{}'.format(component.get('CarModel'))).alignment = align

            # 占用行数汇总
            employ_sum = list()
            for key, value in component.items():
                title_list = ['PowerType', 'manufacturer', 'Price', 'CarModel', 'IsOnSale']
                if set(title_list) > set(component.keys()):
                    employ_sum.append(1)
                    break
                elif key not in title_list:
                    employ_sum.append(len(component.get(key)))

            # 元素值
            component_keys = [key for key in component.keys() if
                              key not in ['PowerType', 'manufacturer', 'Price', 'CarModel', 'IsOnSale']]
            temp_start_row = component_start_row
            for keys in component_keys:

                self.work_sheet.cell(row=temp_start_row, column=6, value='{}'.format(keys))

                if len(component.get(keys)) == 1:
                    for k, v in component.get(keys)[0].items():
                        if k in column_compare_dict.keys():
                            self.work_sheet.cell(row=temp_start_row, column=column_compare_dict.get(k),
                                                 value='{}'.format(v))
                    temp_start_row = temp_start_row + 1
                    print(keys, temp_start_row)
                else:
                    part_merge_row = temp_start_row
                    for part in component.get(keys):
                        print(component)
                        print(part_merge_row, part)
                        for k, v in part.items():
                            if k in column_compare_dict.keys():
                                self.work_sheet.cell(row=part_merge_row, column=column_compare_dict.get(k),
                                                     value='{}'.format(v))
                        part_merge_row = part_merge_row + 1
                    temp_start_row = temp_start_row + len(component.get(keys))

            # 合并单元格
            component_end_row = sum(employ_sum) + component_start_row - 1
            print(component_start_row, component_end_row)
            # 合并单元格
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=1, end_row=component_end_row,
                                        end_column=1)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=2, end_row=component_end_row,
                                        end_column=2)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=3, end_row=component_end_row,
                                        end_column=3)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=4, end_row=component_end_row,
                                        end_column=4)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=5, end_row=component_end_row,
                                        end_column=5)

            # component数据之间添加空格,空格填充背景颜色(绿色)
            if component != self.parse()[-1]:
                background_color = PatternFill("solid", fgColor="00CCFFFF")
                self.work_sheet.cell(row=component_end_row + 1, column=1).fill = background_color
                self.work_sheet.cell(row=component_end_row + 1, column=1, value='{}'.format(''))
                self.work_sheet.merge_cells(start_row=component_end_row + 1, start_column=1,
                                            end_row=component_end_row + 1, end_column=13)
            # print('end row',component_end_row)

            # 下一个起始值
            component_start_row = sum(employ_sum) + component_start_row + 1

    def save_excel(self):
        self.write_excel()
        self.work_book.save("Porsche.xlsx")


if __name__ == '__main__':
    c = YamlToExcel()
    c.save_excel()
